package teacher;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Scanner;

public class teacherLand {
	private static String user;
	private static String password;
	
	private static void land() {
		
		// TODO Auto-generated method stub
		Scanner sc=new Scanner(System.in);
		System.out.print("请输入用户名：");
		
		String use=sc.next();
		System.out.print("请输入密码：");
		String paw=sc.next();
		if(use.equals(user) && paw.equals(password)) {
			two:while(true) {
			System.out.println("**********************************");
			System.out.println("*             1.添加学生信息");
			System.out.println("*             2.查询学生信息");
			System.out.println("*             3.修改学生信息");
			System.out.println("*             4.删除学生信息");
			System.out.println("*             5.保存学生信息");
			System.out.println("*             6.加载学生信息");
			System.out.println("*             0.任意键退出");
			System.out.println("请选择功能菜单");

			System.out.println("**********************************");
			
			int fun=sc.nextInt();
			
			switch(fun) {
			case 1:
				add();
				break;
			case 2:
				query();
				break;
			case 3:
				modify();
				break ;
			case 4:
				delete();
				break ;
			case 5:
				storage();
				break ;
			case 6:
				load();
				break ;
				default:
					break two;
			
			}
			}
			
		}else {
			System.out.println("密码或验证码错误");
		}
				
		
	}
	
	//创建连接数据库
	public static void sql() {
		//JDBC连接数据库
		//导入jdbc驱动
		String dirver="oracle.jdbc.OracleDriver";
		Connection co=null;
		Statement st=null;
		ResultSet re=null;
		try {
			Class.forName(dirver);
			//连接字符串
			String urla="jdbc:oracle:thin:@localhost:1521:orcl11";
			//数据库用户名
			String use="hr";
			//数据库密码
			String paw="oracle";
			co=DriverManager.getConnection(urla,use,paw);
			st=co.createStatement();
			//查询数据库的语句
			String sq="select * from TUSER";
			re=st.executeQuery(sq);
			while(re.next()) {
			user=re.getString(1);
			password=re.getString(2);
			}
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
			
				try {
					if(co !=null)co.close();
					if(re !=null) re.close();
					if(st !=null) st.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		
		land();
		
	}
	
	private static void add() {
		//添加学生信息
		/*
		 * a)	提示用户输入 学生信息 编号，密码（初始默认密码），姓名，性别，年龄，电话，地址
		 *	b)	成功后显示所有学生信息（在添加成功数据之后，重新查询显示所有的学生信息，注意使用倒序显示，将最新插入的学生信息显示在最前面）

		 * */
		Scanner sc=new Scanner(System.in);
		System.out.println("*              添加学生信息");
		
		System.out.println("请输入账号");
		int us=sc.nextInt();
		
		String n=sc.nextLine();
		System.out.println("是否重置密码");
		System.out.println("【Y】【N】");
		String str=sc.nextLine();
		String pass=null;
		if(str.equals("Y")) {
			System.out.println("请输入密码");
			pass=sc.nextLine();
		}
		
		System.out.println("请输入姓名");
		String name=sc.next();
		
		System.out.println("请输入性别");
		String sex=sc.next();
		
		System.out.println("请输入年龄");
		int age=sc.nextInt();
		String m=sc.nextLine();
		
		
		System.out.println("请输入电话");
		String phone=sc.next();
		
		System.out.println("请输入地址");
		String address=sc.next();
		
		
		
		
		Connection conn=null;
		Statement sta=null;
		ResultSet rs=null;
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			String url="jdbc:oracle:thin:@localhost:1521:orcl11";
			String user="hr";
			String paw="oracle";
			
			conn=DriverManager.getConnection(url, user,paw);
			
			sta=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			String sql=null;
			if(str.equals("Y")) {
				sql="insert into student values("+us+",'"+pass+"','"+name+"','"+sex+"',"+age+",'"+phone+"','"+address+"')";
			}else {
				sql="insert into student(stu_id,stu_name,stu_sex,stu_phone,stu_address,stu_age) values("+us+",'"+name+"','"+sex+"','"+phone+"','"+address+"',"+age+")";
			}
			
			
			sql="select * from student";
			rs=sta.executeQuery(sql);
			rs.afterLast();
			while(rs.previous()) {
				System.out.println("编号:"+ rs.getObject(1)+" 姓名:"+rs.getObject(3)+" 性别:"+rs.getObject(4)+" 年龄:"+rs.getObject(5)+" 电话:"+rs.getObject(6)+" 地址:"+rs.getObject(7));
				
			}
			System.out.println();
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	finally{
				try {
					if(conn !=null)conn.close();
					if(sta !=null)sta.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			
		}
		
		
		
		
	}
	private static void query() {
			Connection conn=null;
			Statement sta=null;
			ResultSet re=null;
			String url="jdbc:oracle:thin:@localhost:1521:orcl11";
			String user="hr";
			String pass="oracle";
			
			System.out.println("请选择查询的功能");
			System.out.println("1.按姓名查询");
			System.out.println("2.性别查询");
			System.out.println("3.按年龄段查询");
			System.out.println("4.按编号查询");
			System.out.println("5.按地址查询");
			System.out.println("6.查询全部");
			System.out.println("7.返回上一级");
			Scanner sc=new Scanner(System.in);
			int fun=sc.nextInt();
			String l=sc.nextLine();
			String sql=null;
			three:while(true) {
			switch(fun) {
			case 1:
				System.out.println("请输入你要查询的关键字");
				String st=sc.nextLine();
				sql="select * from student where stu_name like '%"+st+"%'";
				break three;
			case 2:
				System.out.println("请输入你要查询的关键字");
				st=sc.nextLine();
				sql="select * from student where stu_sex like '%"+st+"%'";
				break three;
			case 3:
				System.out.println("请输入你要查询的年龄的范围,用-隔开");
				st=sc.nextLine();
				String[] array=st.split("-");
				int first=Integer.parseInt(array[0]);
				int lest=Integer.parseInt(array[1]);
				sql="select * from student where stu_age between "+first+" and "+lest;
				break three;
			case 4:
				System.out.println("请输入你要查询的关键字");
				st=sc.nextLine();
				sql="select * from student where stu_id like '%"+st+"%'";
				break three;
			case 5 :System.out.println("请输入你要查询的关键字");
				st=sc.nextLine();
				sql="select * from student where stu_address like '%"+st+"%'";
				break three;
			case 6:
				sql="select * from student";
				break three;
			case 7:
				break three;
				default:System.out.println("输入有误，请重新输入");
			
			}
			}
			
			
			
			if(sql != null) {
			
			try {
				Class.forName("oracle.jdbc.OracleDriver");
				conn=DriverManager.getConnection(url, user, pass);
				sta=conn.createStatement();
				re=sta.executeQuery(sql);
				while(re.next()) {
					System.out.println("编号:"+ re.getObject(1)+" 姓名:"+re.getObject(3)+" 性别:"+re.getObject(4)+" 年龄:"+re.getObject(5)+" 电话:"+re.getObject(6)+" 地址:"+re.getObject(7));
					
				}
				
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
			
			
				try {
					if(conn !=null)conn.close();
					if(re !=null) re.close();
					if(sta !=null) sta.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
			
			}
			
		}
	private static void modify() {
		
		Connection conn=null;
		Statement stat=null;
		ResultSet rs=null;
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			String url="jdbc:oracle:thin:@localhost:1521:orcl11";
			String user="hr";
			String password="oracle";
			conn=DriverManager.getConnection(url, user, password);
			stat=conn.createStatement();
			//查询修改前的数据
			String sql="select * from student";
			rs=stat.executeQuery(sql);
			while(rs.next()) {
				System.out.println("编号:"+ rs.getObject(1)+" 姓名:"+rs.getObject(3)+" 性别:"+rs.getObject(4)+" 年龄:"+rs.getObject(5)+" 电话:"+rs.getObject(6)+" 地址:"+rs.getObject(7));

			}
			
			System.out.println("请输入要修改的学生的编号");
			Scanner sc=new Scanner(System.in);
			int id=sc.nextInt();
			
			System.out.println("请输入新的密码");
			String pas=sc.next();
			
			System.out.println("请输入新的姓名");
			String name=sc.next();
			
			System.out.println("请输入新的性别");
			String sex=sc.next();
			
			System.out.println("请输入新的年龄");
			int age=sc.nextInt();
			String m=sc.nextLine();
			
			
			System.out.println("请输入新的电话");
			String phone=sc.next();
			
			System.out.println("请输入新的地址");
			String address=sc.next();
			//stu_pass,stu_name,stu_sex,stu_phone,stu_address,stu_age
			String str="stu_pass='"+pas+"',stu_name='"+name+"',stu_sex='"+sex+"',stu_phone='"+phone+"',stu_address='"+address+"',stu_age="+age;
			//修改语句
			sql="update student set "+str+" where stu_id="+id;
			stat.executeUpdate(sql);
			
			//查询修改后的数据
			sql="select * from student";
			rs=stat.executeQuery(sql);
			while(rs.next()) {
				System.out.println("编号:"+ rs.getObject(1)+" 姓名:"+rs.getObject(3)+" 性别:"+rs.getObject(4)+" 年龄:"+rs.getObject(5)+" 电话:"+rs.getObject(6)+" 地址:"+rs.getObject(7));

			}
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
			
			try {
				if(conn !=null)conn.close();
				if(rs !=null) rs.close();
				if(stat !=null) stat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
		
		
	}
	private static void delete() {
		Connection conn=null;
		Statement stat=null;
		ResultSet re=null;
		
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			String url="jdbc:oracle:thin:@localhost:1521:orcl11";
			String user="hr";
			String password="oracle";
			conn=DriverManager.getConnection(url, user, password);
			stat=conn.createStatement();
			String sql="select * from student";
			re=stat.executeQuery(sql);
			System.out.println("*************删除前的数据**************");
				while(re.next()) {
				System.out.println("编号:"+ re.getObject(1)+" 姓名:"+re.getObject(3)+" 性别:"+re.getObject(4)+" 年龄:"+re.getObject(5)+" 电话:"+re.getObject(6)+" 地址:"+re.getObject(7));
					}
				System.out.println("请选择要删除的学生编号");
				Scanner sc=new Scanner(System.in);
				int id=sc.nextInt();
				sql="delete student where stu_id='"+id+"'";
				stat.executeUpdate(sql);
				System.out.println("*************删除后的数据**************");
				sql="select * from student";
				re=stat.executeQuery(sql);
					while(re.next()) {
					System.out.println("编号:"+ re.getObject(1)+" 姓名:"+re.getObject(3)+" 性别:"+re.getObject(4)+" 年龄:"+re.getObject(5)+" 电话:"+re.getObject(6)+" 地址:"+re.getObject(7));
						}
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
			
			try {
				if(conn !=null)conn.close();
				if(stat !=null) stat.close();
				if(re !=null) re.close();
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
	private static void storage() {
		System.out.println("*********把学生数据存储到相应地址*************");
		System.out.println("请输入你要存储的文件夹的位置");
		Scanner sc=new Scanner(System.in);
		String ad=sc.nextLine();
		System.out.println("请输入保存文件的名称");
		String file=sc.nextLine();
		File fil=new File(ad);
		//判断如果不存在该文件，就创建文件
		if(!fil.isDirectory()) {
			fil.mkdirs();
		}
		File fil2=new File(fil,file);
		//创建字符输出流
		BufferedWriter bw=null;
		if(!fil2.isFile()) {
			try {
				fil2.createNewFile();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		try {
			
			bw= new BufferedWriter(new FileWriter(fil2));
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//获取数据库中的数据
		Connection conn=null;
		Statement stat=null;
		ResultSet re=null;
		String url="jdbc:oracle:thin:@localhost:1521:orcl11";
		String user="hr";
		String password="oracle";
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			conn=DriverManager.getConnection(url,user, password);
			stat=conn.createStatement();
			String sql="select * from student";
			re=stat.executeQuery(sql);
			while(re.next()) {
				
			String str=re.getObject(1)+"---"+ re.getObject(2)+"---"+re.getObject(3)+"---"+re.getObject(4)+"---"+re.getObject(5)+"---"+re.getObject(6)+"---"+re.getObject(7);
			bw.write(str);
			bw.newLine();
			}
				
			
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
			
			try {
				if(conn !=null)conn.close();
				if(stat !=null) stat.close();
				if(re !=null) re.close();
				bw.close();
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
	}
		
		
	}
	
	private static void load() {
		//加载文件夹中的数据信息
		System.out.println("请输入你要加载数据的文件地址及文件");
		Scanner sc=new Scanner(System.in);
		String url=sc.nextLine();
		File fil=new File(url);
		//接收相对文件夹中的数据信息
		if(fil.isFile()) {
		//创建字符输入流
		BufferedReader  br=null;
		try {
			br=new BufferedReader(new FileReader(fil));
			//读取文件夹中的数据,一行一行读取
			
			
			try {
				String str=null;
				while((str=br.readLine() )!=null) {

					String[] array=str.split("---");
					//获取数据，并保存到数据库
					Connection conn=null;
					Statement stat=null;
					try {
						String ur="jdbc:oracle:thin:@localhost:1521:orcl11";
						String user="hr";
						String password="oracle";
						Class.forName("oracle.jdbc.OracleDriver");
						conn=DriverManager.getConnection(ur, user, password);
						stat=conn.createStatement();
						
						try{
						String sql="insert into student values("+array[0]+",'"+array[1]+"','"+array[2]+"','"+array[3]+"',"+array[4]+",'"+array[5]+"','"+array[6]+"')";
						stat.executeUpdate(sql);
						}catch(SQLException e) {
							System.out.println("编号"+array[0]+"添加数据失败");
						}
						System.out.println("加载完成");
						
						
					} catch (ClassNotFoundException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}finally{
						try {
						if(conn !=null)conn.close();
						if(stat !=null) stat.close();
						
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					}
					
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				
				if(br!=null)br.close();
			} 
			catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		}
	}
}
	


